import requests
from selenium import webdriver
import pymysql

def save(isbn, category):
	with open('category.log','a+') as f:
		f.write('%s:%s\n' % (isbn, category))


conn = pymysql.connect(
                host='119.29.82.215',
                port=3306,
                user='root',
                passwd='20158741abc',
                db='app',
                charset='utf8'
            )
cur_select1 = conn.cursor()
cur_select2 = conn.cursor()
cur_update = conn.cursor()
def update(isbn,category):
    sql = 'UPDATE book set category_id = "%s" where isbn = %s' % (category, isbn)
    res = cur_update.execute(sql)
    # print(res)
    if res:
        conn.commit()
        return True
    else:
        conn.rollback()
        return False

def count():
	sql = 'select count(*) from book where category_id = "0"'
	res = cur_select1.execute(sql)
	data = cur_select1.fetchone()
	return data[0]

sql = 'select isbn,name from book where category_id = "0"'
cur_select2.execute(sql)

# 跳过一些查不到的
break_book_number = 317
for x in range(0,break_book_number):
	cur_select2.fetchone()

def get_one():
	data = cur_select2.fetchone()
	if data == None:
		print('Not book to do.')
		return None
	book = {}
	book['isbn'] = data[0]
	book['name'] = data[1]
	return book


browser = webdriver.Chrome()

# init
browser.get('http://opac.nlc.cn/F')
# set select is ISBN
select = browser.find_element_by_name('find_code')
select.send_keys('ISB')
# input isbn
input = browser.find_element_by_name('request')
input.send_keys('9787111542131')
# click search button
btn = browser.find_element_by_xpath('//*[@id="indexpage"]/form/div[2]/input')
btn.click()

not_found_flag = False
k = break_book_number
all = count()
while True:	
	book = get_one()
	k = k+1
	info = '%d/%d' %(k, all)
	if book != None:
		isbn = book['isbn']
		try:
			div = browser.find_element_by_class_name('btn')
			not_found_flag = True
		except:
			not_found_flag = False

		if not_found_flag:
			select = browser.find_element_by_name('find_code')
			select.send_keys('ISB')
			# input isbn
			input = browser.find_element_by_name('request')
			input.clear()
			input.send_keys(isbn)
			# click search button
			div = browser.find_element_by_class_name('btn')
			btn = div.find_element_by_tag_name('input')
			btn.click()
		else:
			input = browser.find_element_by_name('request')
			input.clear()
			input.send_keys(isbn)

			btn = browser.find_element_by_id('lookup')
			btn.click()
		# try:
		list = browser.find_elements_by_class_name('td1')
		category = None
		for i in range(0,len(list)):
			if list[i].text.find('中图分类号') != -1:
				category = list[i+1].text
				break
		if category != None:
			save(isbn, category)
			category = category[:1]
			if 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.find(category) != -1:
				flag = update(isbn=isbn, category=category)
				flag_str = ''
				if flag:
					flag_str = 'True'
				else:
					flag_str = 'False'
				print('%s %s %s : %s ====> %s' %(info,isbn, book['name'], category, flag_str))
			else:
				print('%s %s %s : %s is not extis' %(info, isbn, book['name'], category))
			not_found_flag = False
		else:
			print('%s %s %s : Not found category' %(info, isbn, book['name']))
			not_found_flag = True
		# except:
		# 	print('%s : not found category' % isbn)
		# 	not_found_flag = True
	else:
		print('Not found data')
		break
browser.close()
print('Programe stop.')

